﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using RainyPWS.Data;
using RainyPWS.Common;
using RainyPWS.Model;
using System.Data.Common;

namespace RainyPWS.Business
{
    public class BRecordType
    {
        private static BRecordType _instance = null;
        public static BRecordType Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new BRecordType();
                }
                return _instance;
            }
        }

        public DataTable GetDataType()
        {
            string sql = "select rt.*, IsNull(rc.Count, 0) as Count from TRecordType rt";
            sql += " left join (select count(0) as Count, TypeId from TRecord where [status]<>" + RecordStatus.Deleted + " group by TypeId) rc on rc.TypeId=rt.Id";
            sql += " order by rt.Id asc";
            SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
            try
            {
                return db.ExecuteDataSet(CommandType.Text, sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\r\n" + sql);
            }
        }

        public MRecordType GetRecordTypeModelById(int id)
        {
            string sql = "select * from TRecordType where id=@Id";
            SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@Id", DbType.Int32, id);
            try
            {
                using (IDataReader rdr = db.ExecuteReader(cmd))
                {
                    if (rdr.Read())
                    {
                        return new MRecordType(rdr);
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\r\n" + sql.ToString());
            }
        }

        public int UpdateData(MRecordType obj)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("update TRecordType set");
            sb.AppendLine(" Name=@Name,");
            sb.AppendLine(" Remark=@Remark");
            sb.AppendLine(" where Id=@Id");
            SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
            DbCommand cmd = db.GetSqlStringCommand(sb.ToString());
            db.AddInParameter(cmd, "@Name", DbType.String, obj.Name);
            db.AddInParameter(cmd, "@Remark", DbType.String, obj.Remark);
            db.AddInParameter(cmd, "@Id", DbType.Int32, obj.ID);
            try
            {
                return db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\r\n" + sb.ToString());
            }
        }

        public int InsertData(MRecordType obj)
        {
            string sql = "insert into TRecordType (Name, Remark) values (@Name, @Remark)";
            SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@Name", DbType.String, obj.Name);
            db.AddInParameter(cmd, "@Remark", DbType.String, obj.Remark);
            try
            {
                return db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\r\n" + sql);
            }
        }

        public int DeleteRecordType(int id, out string msg)
        {
            string sql = "select count(0) from TRecord where TypeId=@TypeID";
            string delSql = "delete from TRecordType where ID=@ID";
            SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            DbCommand delCmd = db.GetSqlStringCommand(delSql);
            db.AddInParameter(cmd, "@TypeID", DbType.Int32, id);
            db.AddInParameter(delCmd, "@ID", DbType.Int32, id);
            try
            {
                int count = (int)db.ExecuteScalar(cmd);
                if (count > 0)
                {
                    msg = string.Format("该类别下面有{0}条记事，不可以删除!", count);
                    return 0;
                }
                else
                {
                    msg = "";
                    return db.ExecuteNonQuery(delCmd);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\r\n" + sql + "\r\n" + delSql);
            }
        }

    }

    
}
